// $codepro.audit.disable
/**
 * Class: DATA MANAGER
 * Description:
 * 
 *
 */
import com.google.gdata.client.spreadsheet.ListQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;

import com.google.gdata.data.spreadsheet.CellEntry;

import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;

import java.util.ArrayList;
import java.util.List;

import java.util.logging.ConsoleHandler;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import com.google.gdata.client.spreadsheet.CellQuery;

import com.google.gdata.data.spreadsheet.CellFeed;



  /**
   * @author Team Awesome Express
   * @version $ Revision 1.0 $
   */
  public class DataManager {
	  
	 /**
	  * Field myDataManager.
	  */
	 private static DataManager myDataManager;
	 
	 /**
	  * Field myService.
	  */
	 protected static SpreadsheetService myService;
	 
	 /**
	 * @return the userType
	 */
	public int getUserType() {
		return userType;
	}

	/**
	 * @param userType the userType to set
	 */
	public void setUserType(int userType) {
		DataManager.userType = userType;
	}

	/**
	  * Field entryList.
	  */
	 protected List<SpreadsheetEntry> entryList;
	 
	 /**
	  * Field entry.
	  */
	 protected static SpreadsheetEntry entry;
	 
	 /**
	  * Field username.
	  */
	 protected String username;
	 
	 /**
	  * Field password.
	  */
	 protected String password;
	 
	 /**
	  * Field userType.
	  */
	 protected static int userType;
	 
	 /**
	  * Field table.
	  */
	 protected CellBasedSpreadsheetPanel table;
	 
	 /**
	  * Field cellFeedUrl.
	  */
	 protected URL cellFeedUrl;
	 
	 /**
	  * Field dataList.
	  */
	 private ArrayList<String> dataList;

	/**
	 * Field myLogger.
	 */
	private static final Logger myLogger = Logger.getLogger("./LoginFrame");
		

	 
	 /**
	  * Method initializeSpread.
	  */
	 public void initializeSpread() {
		 try {
		        // Create a new Spreadsheet service
		        myService = new SpreadsheetService("Database Manager");

		        // myService.setUserCredentials("username@gmail.com", "password");
		        myService.setUserCredentials("TeamAwesomeExpress@gmail.com", "muggsey08");    
		        // Get a list of all entries
		        URL metafeedUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/private/full");
		      
		        SpreadsheetFeed resultFeed = myService.getFeed(
		        			metafeedUrl,
		        			SpreadsheetFeed.class
		        			);
		        List<SpreadsheetEntry> entryList = resultFeed.getEntries();	//Returns all spreadsheets
		        entry = entryList.get(0);	//This is the actual database.    

		      }
		      catch(AuthenticationException e) {
		        e.printStackTrace();
		      }
		      catch(MalformedURLException e) {
		        e.printStackTrace();
		      }
		      catch(ServiceException e) {
		        e.printStackTrace();
		      }
		      catch(IOException e) {
		        e.printStackTrace();
		      }
		     
	 }
	 
	 static {
         
         ConsoleHandler ch = new ConsoleHandler();
         ch.setLevel(Level.ALL);
         ch.setFormatter(new SimpleFormatter());
         myLogger.addHandler(ch);
         myLogger.setLevel(Level.ALL);
         myLogger.setUseParentHandlers(true);
         
         try {
             FileHandler fh = new FileHandler("Login.html");
             fh.setFormatter(new HTMLFormatter());
             myLogger.addHandler(fh);
         } catch (SecurityException e) {
             myLogger.log(Level.SEVERE, "Security Exception creating a file handler", e);
             e.printStackTrace();
         } catch (IOException e) {
             myLogger.log(Level.SEVERE, "IO Exception creating a file handler", e);
             e.printStackTrace();
         } 
         
     }
	 
	 /**
 	 * Instantiates a new data manager with a username the pass
 	 *
 	 * @param username the username 
 	 * @param password the password
 	 */
 	private DataManager(String username, String password) {
		 this.username = username;
		 this.password = password;
		 initializeSpread();
	 }
	 
	 /**
	  * Creates a datamanager and authenticates databse
	  */
	 public DataManager() { // Used for new patients sign up
		 initializeSpread();
	 }
	 
	 /**
	  * Method getDataManager.
	  * @param username String
	  * @param password String
	  * @return DataManager
	  */
	 public static DataManager getDataManager(String username, String password){
		 if (myDataManager == null){
			 myDataManager = new DataManager(username, password);
		 }
		 return myDataManager;
	 }
	 /**
	  * 
	  * @param userType
	  * @param sheetType
	  */
	 public void initializeTable(int userType, int sheetType) { // Used to create editable tables
		 try {
		        myService = new SpreadsheetService("Table");
		        myService.setUserCredentials("TeamAwesomeExpress@gmail.com", "muggsey08");

		        // Restarts the Spreadsheet Service and opens the sheet again.
		        URL metafeedUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/private/full");      
		        SpreadsheetFeed resultFeed = myService.getFeed(
		        			metafeedUrl,
		        			SpreadsheetFeed.class
		        			); // Get a list of spreadsheets
		        List<SpreadsheetEntry> entries = resultFeed.getEntries(); // Put each spreadsheet entry in a arraylist
		        entry = entries.get(0); // Grab the first spreadsheet (doctor's office)
		        
		        if (userType == GlobalVars.NURSE_USERTYPE) {
		        	cellFeedUrl = entry.getWorksheets().get(sheetType).getCellFeedUrl(); // Grab the Correct Worksheet based on sheettype
		        }
		        
		        else if (userType == GlobalVars.DOCTOR_USERTYPE) {
		        	cellFeedUrl = entry.getWorksheets().get(sheetType).getCellFeedUrl(); // Grab the Correct Worksheet based on sheettype
		        }
		        
		        else if (userType == GlobalVars.ADMIN_USERTYPE) {
		        	cellFeedUrl = entry.getWorksheets().get(sheetType).getCellFeedUrl(); // Grab the Correct Worksheet based on sheettype
		        }
		        

		         table = new CellBasedSpreadsheetPanel(
		        		 	myService, 
		        		 	cellFeedUrl, 
		        		 	userType, 
		        		 	sheetType
		        		 	);
		         
		         table.createWindow(myService, cellFeedUrl);
		           
		        
		 }
		 catch (Exception e) {
			 myLogger.log(Level.SEVERE, "EXCEPTION", e);
			 //return;  to avoid empty catch clause rule
		 }
	 }

	  /**
	   * 
	   * @param username
	   * @param password
	  
	   * @return 1 if nurse,
	   * 		 2 if patient,
	   * 		 3 if doctor,
	   * 		 4 if system administrator,
	   * 		 5 is account is locked
	   * 		 -1 if failed */
	  public synchronized int login(String username, String password) {
		  try {
			  initializeSpread();
		       myService = new SpreadsheetService("Database Manager");
		     
		        myService.setUserCredentials("TeamAwesomeExpress@gmail.com", "muggsey08");
		        
		        // TEST
		   //  URL cellFeedUrlTest = entry.getWorksheets().get(7).getListFeedUrl(); // Open patients sheet
//		     ListQuery queryTest = new ListQuery(entry.getWorksheets().get(6).getListFeedUrl());
//		     queryTest.setSortColumn("First Name");
		        // END TEST
		     
		  	URL cellFeedUrl = entry.getWorksheets().get(0).getCellFeedUrl(); // This opens the admin Sheet
		   
	        CellQuery query = new CellQuery(cellFeedUrl);
		  	query.setFullTextQuery(username); // search for username

	        CellFeed feed = myService.query(query, CellFeed.class); // Will create a new CellFeed containing the CellFeed returned by searching for the username
	 
	       ArrayList<CellEntry> cellList = new ArrayList<CellEntry>(); // hold cell entries found from query
	       cellList.addAll(feed.getEntries());
	    
	       
	      	if (cellList.size() == 0){ // Did not find username
	      		
	      		System.out.println("Username does not exist");
	      		return -1;
	      	}
	    	   
	    	   
	       CellQuery newQuery = new CellQuery(cellFeedUrl); // new query using current row and column to find data via set row
	       
	       newQuery.setMinimumRow(cellList.get(0).getCell().getRow());
	       newQuery.setMaximumRow(cellList.get(0).getCell().getRow());
	       
	       newQuery.setMinimumCol(2);	// Column 2 is where passwords are stored	//cellList.get(0).getCell().getCol() + 1); 
	       newQuery.setMaximumCol(5);   // Column 3 is where userType is stored. e.g. "nurse", "patient"
	       								// Column 5 stores login Attempts
	       								// COlumn 4 Stores whether the account is locked or not
	
	  
	       feed = myService.query(newQuery, CellFeed.class); // Bounded by column and row query
	       cellList.clear(); // empty the arraylist of previous data

	       cellList.addAll(feed.getEntries()); // put all cell data found within the bounded region
		 
	   	for (CellEntry cells : cellList) { // Go through list of Passwords and usertypes
	   		
   			if (!cellList.get(2).getCell().getInputValue().equals("Unlocked")) { // Checks if account is locked: third entry in the array list is the account status e.g. "Locked"
					myLogger.log(Level.WARNING, "User " + username + " account has been locked, please contact a System administrator.");
					return -1; // Account is locked
				}
   			
	   		if (password.equals(cells.getCell().getInputValue())){ // Correct username and password
	   			
					CellEntry entry = new CellEntry(
									cellList.get(3).getCell().getRow(),
									cellList.get(3).getCell().getCol(),
									"0"
									); // Sets amount of login Attempts back to zero
   					myService.insert(cellFeedUrl, entry);
	   				
	   				int foundCellIndex = cellList.indexOf(cells); // Returns index in cellList array of the password, the next entry in the cellList would be the userType
	   				   				
	   				if("Patient".equals(cellList.get(foundCellIndex + 1).getCell().getInputValue())) { 
	   					myLogger.log(Level.FINE, "User " + username + " successfully logged in as a Patient.");
	   					userType = GlobalVars.PATIENT_USERTYPE;  // 1 is for patient
	   					
	   				}
	   				else if("Nurse".equals(cellList.get(foundCellIndex + 1).getCell().getInputValue())) {
	   					myLogger.log(Level.FINE, "User " + username + " successfully logged in as a Nurse.");
	   					userType = GlobalVars.NURSE_USERTYPE; // 2 is for nurse
	   					
	   				}
	   				
	   				else if ("Doctor".equals(cellList.get(foundCellIndex + 1).getCell().getInputValue())) { 
	   					myLogger.log(Level.FINE, "User " + username + " successfully logged in as a Doctor.");
	   					userType = GlobalVars.DOCTOR_USERTYPE; // 3 is for Doctor	   
	   									
	   				}
	   				
	   				else if ("Admin".equals(cellList.get(foundCellIndex + 1).getCell().getInputValue())) { 
	   					myLogger.log(Level.FINE, "User " + username + " successfully logged in as an Admin.");
	   					userType = GlobalVars.ADMIN_USERTYPE; // 4 is for System Administrator
	   					 
	   				}
	   				return userType;
	   		}
	   		
	   		else {
	   			String currentLoginAttempts = cellList.get(3).getCell().getInputValue(); // The fourth entry in the array list will be the # login Attempts
	   			Integer loginAttempts = Integer.parseInt(currentLoginAttempts) + 1;
	   			
	   				if (loginAttempts.toString().equals("3")) { // Checks for amount of invalid login Attempts
	   					CellEntry entry = new CellEntry(
	   								cellList.get(2).getCell().getRow(),
	   								cellList.get(2).getCell().getCol(),
	   								"Locked"
	   								); //  Third entry in array list is account status
	   				//	myLogger.log(Level.WARNING, "User " + username + " account has been locked, please contact a System administrator."); 
	   					myService.insert(cellFeedUrl, entry);
	   					JFrame frame = new JFrame();
	   					JOptionPane.showMessageDialog(frame,
	   						    "You have reached the maximum amount of login attempts",
	   						    "Contact a System Administrator",
	   						    JOptionPane.ERROR_MESSAGE);
	   				}
	   				
					CellEntry entry = new CellEntry(cellList.get(3).getCell().getRow(), cellList.get(3).getCell().getCol(), loginAttempts.toString());
   					myService.insert(cellFeedUrl, entry);
	
	   			myLogger.log(Level.WARNING, "User " + username + " has failed to log in");
	   			return -1;
	   		}
	   		} // end for
	 	} // end try block
	      catch(AuthenticationException e) {
	          e.printStackTrace();
	        }
	        catch(MalformedURLException e) {
	        	
	          e.printStackTrace();
	        }
	        catch(ServiceException e) {
	          e.printStackTrace();
	        }
	        catch(IOException e) {
	          e.printStackTrace();
	        }
	   	return -1;
	  } // end Login
	  
	  
	  /**
	   * Grabs a treatment array from the datamanager
	   * @param month The month to get the record for
	   * @return
	   */
	  public TreatmentRecord[] getTreatmentRecords(int month){
		  TreatmentRecord myTreatmentRecordList[];
		  String rawDateData[] = getDataAtCol(GlobalVars.TREATMENT_RECORD_SHEET, "Appointment Date", "Appointment Date");
		  myTreatmentRecordList = new TreatmentRecord[rawDateData.length];
		  int recordNumber= 0;
		  for (int i=0;i<rawDateData.length;i++){
			  if (month < 9){ 
				  String string1 = rawDateData[i].substring(0, 2);
				  String string2 =  ((Integer)month) + "/";
				  if (string1.equals(string2)){
					  String[] rowInformation = getDataAtRow(GlobalVars.TREATMENT_RECORD_SHEET, 1, 16, i+2 );
					  myTreatmentRecordList[recordNumber] = new TreatmentRecord(rowInformation);
					  recordNumber++;
				  }
			  }
			  else{
				  if (rawDateData[i].substring(0, 1) == ((Integer)month).toString()){
					  myTreatmentRecordList[recordNumber] = new TreatmentRecord(getDataAtRow(GlobalVars.TREATMENT_RECORD_SHEET, 1, 16, i+2 ));
					  recordNumber++;
				  }
				  
			  }
			  
		  }
		  TreatmentRecord finalList[] = new TreatmentRecord[recordNumber];
		  for (int j=0;j<finalList.length;j++){
			  finalList[j]= myTreatmentRecordList[j];
		  }
		  return finalList;
	  }
	  
	  
	  /**
	   * Get user's First and Last name
	   * 
	   * @param username 
	   * @param usertype 1 patient, 2 nurse, 3 doctor, 4 sys. Admin
	  
	   * @return First name + Last name */
	  public synchronized String userInfo(String username, int usertype) { 
          String name = "";
		  try {
			  initializeSpread(); // Check
			  myService = new SpreadsheetService("Database Manager");
	
			  myService.setUserCredentials("TeamAwesomeExpress@gmail.com", "muggsey08");
			  URL cellFeedUrl = entry.getWorksheets().get(usertype).getCellFeedUrl(); // This opens the patient sheet
				   
			  CellQuery query = new CellQuery(cellFeedUrl);
			  query.setFullTextQuery(username); // search for username

			  CellFeed feed = myService.query(query, CellFeed.class); // Will create a new CellFeed containing the CellFeed returned by searching for the username
			 
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>(); // hold cell entries found from query
			  cellList.addAll(feed.getEntries());
			          
			  CellQuery newQuery = new CellQuery(cellFeedUrl); // new query using current row and column to find data via set row
			  newQuery.setMinimumRow(cellList.get(0).getCell().getRow());
			  newQuery.setMaximumRow(cellList.get(0).getCell().getRow());
			       
			  newQuery.setMinimumCol(1);	// Column 1 is where Username is stored 
			  newQuery.setMaximumCol(3);   // Column 3 is where Lastname is stored
			       						
			  feed = myService.query(newQuery, CellFeed.class); // Bounded by column and row query
              cellList.clear(); // empty the arraylist of previous data

			  cellList.addAll(feed.getEntries()); // put all cell data found within the bounded region
		
			   			name = cellList.get(1).getCell().getValue() + " " +
			   			       cellList.get(2).getCell().getValue();

			//   } // end for
	     } // end try block
		 catch(AuthenticationException e) {
		      e.printStackTrace();
		 }
		 catch(MalformedURLException e) {
		 	  e.printStackTrace();
	     }
		 catch(ServiceException e) {
		      e.printStackTrace();
		 }
		 catch(IOException e) {
		      e.printStackTrace();
		 }
		 return name;
	  } // end userinfo
	  


	/**
	 * 
	 * @param userId
	 * @param password
	 * @param usertype
	 * @param accountStatus
	 * @param loginAttempts
	 */
	  public synchronized void createUserData(String userId, String password, String usertype, String accountStatus, String loginAttempts){
		  try{
			  dataList = new ArrayList();
			  int numOfCol = 5; //5 Columns of the usersheet
			  URL userCellFeedUrl = entry.getWorksheets().get(GlobalVars.USER_SHEET).getCellFeedUrl(); // This opens the User Sheet
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>(); // hold cell entries found from query
			  cellList.clear(); // empty the list to take in the new entries - User sheet entries
			  CellQuery userQuery = new CellQuery(userCellFeedUrl);
			  userQuery.setFullTextQuery("");
			  CellFeed userFeed = myService.query(userQuery, CellFeed.class);
			  cellList.addAll(userFeed.getEntries());
			  int row = (cellList.size() / numOfCol + 1); 
		  	  dataList.add(userId);
		  	  dataList.add(password);
		  	  dataList.add(usertype);
		  	  dataList.add(accountStatus);
		  	  dataList.add(loginAttempts);
			  for(int i=0; i < 5; i++){
		   		CellEntry input = new CellEntry(row, i + 1, dataList.get(i)); 
	   			myService.insert(userCellFeedUrl, input);
		  } // end for
	
		  }
		  catch(AuthenticationException e) {
	          e.printStackTrace();
	        }
	        catch(MalformedURLException e) {
	        	
	          e.printStackTrace();
	        }
	        catch(ServiceException e) {
	          e.printStackTrace();
	        }
	        catch(IOException e) {
	          e.printStackTrace();
	        }
		  
	  }
	  
	  
	  
	  
	  /**
	  
	   * @param whichSheet Which sheet you're looking for row numbers
	   * @param colNumber
	   * @param keyWord
	  
	   * @return rowNumbers 
	   * */
	 
	  public synchronized int[] getRowNumbers(int whichSheet, int colNumber, String keyWord){
		  int[] rowNumbers = null;
		  try{
			  URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl(); //Gets the cellFeed
			  CellQuery myQuery = new CellQuery(cellFeedUrl);
			  myQuery.setFullTextQuery(keyWord);
			  myQuery.setMinimumCol(colNumber);	//Search only this column
			  myQuery.setMaximumCol(colNumber);	//Search only this column
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
			  CellFeed feed = myService.query(myQuery, CellFeed.class);
              cellList.addAll(feed.getEntries());
              rowNumbers = new int[cellList.size()];	//initialize string 
              
              for(int i=0; i < cellList.size(); i++){
            	  rowNumbers[i] = cellList.get(i).getCell().getRow();
              }
              return rowNumbers;
			  
			  
		  }
		  catch(AuthenticationException e) {
              e.printStackTrace();
         }
         catch(MalformedURLException e) {                       
                  e.printStackTrace();
         }
         catch(ServiceException e) {
              e.printStackTrace();
         }
         catch(IOException e) {
              e.printStackTrace();
         }
		  return rowNumbers;
	  }
	  /**
	   * set data in the empty sheet (not replacing the data)
	   * @param whichSheet sheet which data will be saved
	   * @param p a patient object
	   * @param data list of data
	   * @param rowNumber the row number to set the data at
	   */
	  public synchronized void setData(int whichSheet, Person p, String[] data, int rowNumber) {
		  try {
			  int row;
			  int numOfCol = 0;
			  switch(whichSheet){
			  case GlobalVars.USER_SHEET: numOfCol = 4; //User sheet
			  	break;
			  case GlobalVars.PATIENT_SHEET: numOfCol = 12; //Patient sheet
			  	break;
			  case GlobalVars.TREATMENT_RECORD_SHEET: numOfCol = 16; //Treatment record sheet
			  	break;
			  case GlobalVars.APPOINTMENT_SHEET: numOfCol = 7; //number of column used for this worksheet
			  	break;
			  case GlobalVars.NURSE_SHEET: numOfCol = 3;
			    break;
			  case GlobalVars.DOCTOR_SHEET: numOfCol = 3;
			    break;
			  case GlobalVars.ADMIN_SHEET: numOfCol = 3;
			    break;
			  }
			  initializeSpread(); // Authenticate google database
	
			  URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl(); // This opens the appropiate WorkSheet			   
			  CellQuery query = new CellQuery(cellFeedUrl);
			  query.setFullTextQuery("");

			  CellFeed feed = myService.query(query, CellFeed.class);
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>(); // hold cell entries found from query
			  cellList.addAll(feed.getEntries());
			  
			  if (rowNumber == -1) { // no rowNumber passed in
				  row = ((cellList.size() + 2) / numOfCol) + 1;  // Next empty row in database
			  }
			  else {
				  row = rowNumber; // Used to edit existing row in database
			  }
			 
			  
			  if (data != null) {
				  for(int i=0; i < numOfCol; i++){
					    if (i == data.length) { // Check if it is out of bounds
					    	return;
					    }
				   		CellEntry input = new CellEntry(row, i + 1, data[i]); 
			   			myService.insert(cellFeedUrl, input);
				  } // end for
				  return; // No object so break out of method
			  }
			  
			 
			  dataList = fillPatientArray(p); // Fills array with patient data
			  
			  for(int i=0; i < numOfCol; i++){
			   		CellEntry input = new CellEntry(row, i + 1, dataList.get(i)); 
		   			myService.insert(cellFeedUrl, input);
			  } // end for
			  
			  
	 	} // end try block
	      catch(AuthenticationException e) {
	          e.printStackTrace();
	        }
	        catch(MalformedURLException e) {
	        	
	          e.printStackTrace();
	        }
	        catch(ServiceException e) {
	          e.printStackTrace();
	        }
	        catch(IOException e) {
	          e.printStackTrace();
	        }
	  } 
	  /**
	   *Get list of data within the range of rows
	   *@param whichSheet which sheet to look up
	   *@param colStart beginning of the row
	   *@param colEnd end of the row
	   *@param rowNumber which row to return
	   *
	   *
	   *
	  @return **/
	  public synchronized String[] getDataAtRow(int whichSheet, int colStart, int colEnd, int rowNumber){
		  String[] myStringArray= null;
		  try{
			  URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl(); //Gets the cellFeed
			  CellQuery myQuery = new CellQuery(cellFeedUrl);
			  myQuery.setMinimumRow(rowNumber);	//Only get this one Row
			  myQuery.setMaximumRow(rowNumber);	//Only get this one Row
			  myQuery.setMinimumCol(colStart);	//Starts at this column
			  myQuery.setMaximumCol(colEnd);	//Ends at this column
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
			  CellFeed feed = myService.query(myQuery, CellFeed.class);
              cellList.addAll(feed.getEntries());
              myStringArray = new String[cellList.size()];	//initialize string 
              
              for(int i=0; i < cellList.size(); i++){
            	  myStringArray[i] = cellList.get(i).getCell().getValue();
              }
              return myStringArray;
			  
			  
		  }
		  catch(AuthenticationException e) {
              e.printStackTrace();
         }
         catch(MalformedURLException e) {                       
                  e.printStackTrace();
         }
         catch(ServiceException e) {
              e.printStackTrace();
         }
         catch(IOException e) {
              e.printStackTrace();
         }

		  return myStringArray;
	  
	  }
	  
	  
	  /**
       * Get list of data within the range of columns
       * @param whichSheet which sheet to look up
       * @param colFrom beginning of the column's title
       * @param colTo end of the column's title 
      
       * @return list of data within the range of columns */
      public synchronized String[] getDataAtCol(int whichSheet, String colFrom, String colTo) {
    	  String[] dataListAtCol;
              try {
                      URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl(); // This opens the patient sheet
                                 
                      int startCol = getColumnPos(whichSheet, colFrom);	//get specified column's position in the worksheet
                      int endCol = getColumnPos(whichSheet, colTo);	//get specified column's position in the worksheet
                      
                      CellQuery query = new CellQuery(cellFeedUrl);
                      query.setFullTextQuery(""); // search for written data

                      CellFeed feed = myService.query(query, CellFeed.class);
                         
                      ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
                      cellList.addAll(feed.getEntries());
                                  
                      CellQuery newQuery = new CellQuery(cellFeedUrl);
                      
                      newQuery.setMinimumRow(2);
                               
                      newQuery.setMinimumCol(startCol);  
                      newQuery.setMaximumCol(endCol);
                                                            
                      feed = myService.query(newQuery, CellFeed.class);
                      cellList.clear();

                      cellList.addAll(feed.getEntries());
                      
                      dataListAtCol = new String[cellList.size()];	//initialize string 
                      for(int i=0; i < cellList.size(); i++){
                    	  dataListAtCol[i] = cellList.get(i).getCell().getValue();
                      }

                      return dataListAtCol;
                 } // end try block
                 catch(AuthenticationException e) {
                      e.printStackTrace();
                 }
                 catch(MalformedURLException e) {                       
                          e.printStackTrace();
                 }
                 catch(ServiceException e) {
                      e.printStackTrace();
                 }
                 catch(IOException e) {
                      e.printStackTrace();
                 }
                 dataListAtCol = new String[0]; //An array of size 0???
                 return dataListAtCol;
          } // end userinfo
      
      /**
       * Find specified comlumn's position in the worksheet
       * 
       * @param whichSheet which sheet to look up
       * @param whichCol column title like "UserName*", "Last Name"
      
       * @return position of specified column */
      public synchronized int getColumnPos(int whichSheet, String whichCol){
    	  int colNum = -1;
    	  try{
              URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl(); 
              CellQuery query = new CellQuery(cellFeedUrl);
              query.setFullTextQuery(whichCol); // search for column title in the worksheet

              CellFeed feed = myService.query(query, CellFeed.class);
             
              ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
              cellList.addAll(feed.getEntries());
              
              CellQuery newQuery = new CellQuery(cellFeedUrl);
              
              newQuery.setMinimumRow(0);	//set range only at first row to compare column title
              newQuery.setMaximumRow(0);
                                                               
              feed = myService.query(newQuery, CellFeed.class);
              cellList.clear();

              cellList.addAll(feed.getEntries());

              for (CellEntry cells : cellList){
            	  if(whichCol.equals(cells.getCell().getValue())){
            		  colNum = cells.getCell().getCol();
            	  }
              }
    	  }
    	  catch(AuthenticationException e) {
              e.printStackTrace();
         }
         catch(MalformedURLException e) {                       
                  e.printStackTrace();
         }
         catch(ServiceException e) {
              e.printStackTrace();
         }
         catch(IOException e) {
              e.printStackTrace();
         }
         
         return colNum;
      }
      
      /**
       * After delete one row, this method moves data list and fill out empty row
       * 
       * @param whichSheet sheet which needs to move data list
       * @param rowNum deleted row number
       */
      public synchronized void moveDataList(int whichSheet, int rowNum){
    	  try{
    		  int numOfCol=0;
    		  switch(whichSheet){
			  case GlobalVars.USER_SHEET: numOfCol = 4;
			  		break;//User sheet
			  case GlobalVars.PATIENT_SHEET: numOfCol = 12;
			  		break;//Patient sheet
			  case GlobalVars.TREATMENT_RECORD_SHEET: numOfCol = 16; 
			  		break;	//Treatment record sheet 
			  case GlobalVars.APPOINTMENT_SHEET: numOfCol = 7; 
			  		break;	//number of column used for this worksheet
			  }
			  URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl();
			  
			  CellQuery query = new CellQuery(cellFeedUrl);
			  query.setFullTextQuery("");	//search for given userID
			  
			  CellFeed feed = myService.query(query, CellFeed.class);
			  
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
			  cellList.addAll(feed.getEntries());
			  int length = cellList.size();
			  int maxRow = length / numOfCol + 1;
              
			  while(rowNum < maxRow){
				  String[] rowData = new String[numOfCol];
		          CellQuery newQuery = new CellQuery(cellFeedUrl);
              
		          newQuery.setMinimumRow(rowNum + 1);
	              newQuery.setMaximumRow(rowNum + 1);
	              newQuery.setMinimumCol(1);
	              newQuery.setMaximumCol(numOfCol);
	                                                                   
	              feed = myService.query(newQuery, CellFeed.class);
	              cellList.clear();
	
	              cellList.addAll(feed.getEntries());
	              
	              //copy data at indicated row
	              int i=0;
		          for (CellEntry cells : cellList){
		        	 rowData[i] = cells.getPlainTextContent();
		        	 i++;
		          }
		          //insert copied data to row
	              if (rowData != null) {
					  for(int j=0; j < numOfCol; j++){
						    if (j == rowData.length) {
						    	return;
						    }
					   		CellEntry input = new CellEntry(rowNum, j + 1, rowData[j]); 
				   			myService.insert(cellFeedUrl, input);
					  }
				  }
		          rowNum++;
              }
			  
			  //delete last row since data list has been moved
			  for(int i=0; i < numOfCol; i++){
				  CellEntry cells = new CellEntry(maxRow, i + 1, "");
				  myService.insert(cellFeedUrl, cells);
			  }
    	  }
    	  
    	 catch(AuthenticationException e) {
              e.printStackTrace();
         }
         catch(MalformedURLException e) {                       
                  e.printStackTrace();
         }
         catch(ServiceException e) {
              e.printStackTrace();
         }
         catch(IOException e) {
              e.printStackTrace();
         }

      }

      /**
       * This method deletes one row in the worksheet you indicated.
       * 
       * @param whichSheet sheet which needs to delete row
       * @param data comparable data. In this case, this would be usually userID.
       */
      public synchronized void deleteRow(int whichSheet, String data){
    	  try{
    		  int numOfCol=0;
    		  switch(whichSheet){
			  case GlobalVars.USER_SHEET: numOfCol = 4;
			  		break;//User sheet
			  case GlobalVars.PATIENT_SHEET: numOfCol = 12;
			  		break;//Patient sheet
			  case GlobalVars.TREATMENT_RECORD_SHEET: numOfCol = 16; 
			  		break;	//Treatment record sheet 
			  case GlobalVars.APPOINTMENT_SHEET: numOfCol = 7; 
			  		break;	//number of column used for this worksheet
			  }
			  URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl();
			   
			  CellQuery query = new CellQuery(cellFeedUrl);
			  query.setFullTextQuery(data);	//search for given userID
			  
			  CellFeed feed = myService.query(query, CellFeed.class);
			  
			  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
			  cellList.addAll(feed.getEntries());
			                    			  
              CellQuery newQuery = new CellQuery(cellFeedUrl);
             
              int deletedRow = cellList.get(0).getCell().getRow();
              
              newQuery.setMinimumRow(cellList.get(0).getCell().getRow());
              newQuery.setMaximumRow(cellList.get(0).getCell().getRow());
              newQuery.setMinimumCol(1);
              newQuery.setMaximumCol(numOfCol);
                                                                   
              feed = myService.query(newQuery, CellFeed.class);
              cellList.clear();

              cellList.addAll(feed.getEntries());
              
	          for (CellEntry cells : cellList){
	        	  cells.delete();
	          }
	          moveDataList(whichSheet, deletedRow);
	          
    	  }
    	  
    	 catch(AuthenticationException e) {
              e.printStackTrace();
         }
         catch(MalformedURLException e) {                       
                  e.printStackTrace();
         }
         catch(ServiceException e) {
              e.printStackTrace();
         }
         catch(IOException e) {
              e.printStackTrace();
         }
      }
      
      public int getNumRows(int whichSheet){
    	  try{
    		  int numOfCol=0;
    		  switch(whichSheet){
			  case GlobalVars.USER_SHEET: numOfCol = 4;
			  		break;//User sheet
			  case GlobalVars.PATIENT_SHEET: numOfCol = 12;
			  		break;//Patient sheet
			  case GlobalVars.TREATMENT_RECORD_SHEET: numOfCol = 16; 
			  		break;	//Treatment record sheet 
			  case GlobalVars.APPOINTMENT_SHEET: numOfCol = 7; 
			  		break;	//number of column used for this worksheet
			  
			  }
    		  URL cellFeedUrl = entry.getWorksheets().get(whichSheet).getCellFeedUrl();
    		  CellQuery query = new CellQuery(cellFeedUrl);
    		  query.setFullTextQuery("");
    		  CellFeed feed = myService.query(query, CellFeed.class);
    		  ArrayList<CellEntry> cellList = new ArrayList<CellEntry>();
			  cellList.addAll(feed.getEntries());
    		  return (cellList.size()/numOfCol) -1;
    		  
    		  
    	  }
    	  catch(Exception e){}
		return 0;
      }
	  /**
	   * Fills an ArrayList with patient data
	   * @param p A patient object
	  
	   * @return An array filled with patient data */
	  public ArrayList<String> fillPatientArray(Person p) {
		  dataList = new ArrayList<String>();
		  
		  dataList.add(p.getLoginName());
		  dataList.add(p.getName());
		  dataList.add(p.getLastname());
		  dataList.add(p.getGender());
		  dataList.add(p.getAge());
		  dataList.add(p.getSocialSecNum());
		  dataList.add(p.getPhoneNum());
		  dataList.add(p.getAddress());
		  dataList.add(p.getEmail());
		  dataList.add(((Patient) p).getAllergies());
		  dataList.add(((Patient) p).getPharmacy());
		  dataList.add(((Patient) p).getInsurance());
		  return dataList;
	  }
	  
	 /**
	  * Create a string array of data from a treatment record
	  * @param t a treatmentRecord
	 
	  * @return data A string array of a treatment record 
	  */
	  public synchronized String[] fillTreatmentArray(TreatmentRecord t) { // Creates a treatment record array to pass into setData
		  
		  String[] data = {t.getPatientUsername(), t.getTreatingDoctor(), t.getCreationDate(), t.getCreationTime(), t.getAttendingNurse(), t.getHeight(),
				  t.getWeight(), t.getComplaint(), t.getBloodPressure(), t.getPulse(), t.getTemperature(), t.getDiagnosis()};
		  
		  return data;
		  
	  }
	  
	  public String[] fillTreatmentArrayDoctor(TreatmentRecord t) { // Creates a treatment record array with Doctors orders
		  
		  String[] data = {t.getPatientUsername(), t.getTreatingDoctor(),  t.getCreationDate(), t.getCreationTime(), t.getAttendingNurse(), t.getHeight(),
				  t.getWeight(), t.getComplaint(), t.getBloodPressure(), t.getPulse(), t.getTemperature(), t.getDiagnosis(), t.getPrescription(), ((Integer)t.getLabWork()).toString(), t.getFollowUpInstructions(), t.getOtherInstructions()};
		  
		  return data;  
	  }
	  

	  
  }
	  

  
  
  
